package dao;

import java.util.Calendar;
import java.util.Date;
import org.hibernate.HibernateException;
import entity.AuxENT;

public class AuxDAO extends GenericDAO {

	public AuxENT getSaldoMes(int usuario, String dia, int id_contaBanco, String tipo) {
		
		try {
			Calendar cal = Calendar.getInstance(); 
			cal.setTime(new Date());
			
			int mes = cal.get(Calendar.MONTH)+1;
			String ano = ""+cal.get(Calendar.YEAR);
			cal.add(Calendar.MONTH, -3);  
			//int lastDay = cal.getActualMaximum(Calendar.DAY_OF_MONTH);  
			String dataInicial =  "01"+"/"+mes+"/"+ano.substring(2,4)+" 00:00:00";
			String dataFinal =     dia+"/"+mes+"/"+ano.substring(2,4)+" 23:59:59";
			
			String sql = "select sum(p1.valor_pagar) as valor                                               "
						+ "from   gff.previsao p1	                                                        "
						+ "where  p1.id_usuario = :id_usuario                                               "
						+ "and	  p1.id_conta_banco = :id_conta_banco                                       "
						+ "and	  p1.tipo = :tipo                                                           "
						+ "and	  p1.data_vencimento between date_format(:dataInicial,'%d/%m/%y %T')        "
						+ "		 			             and date_format(:dataFinal,'%d/%m/%y %T')          ";
			
			query = sessao.createSQLQuery(sql).addEntity("previsao",AuxENT.class);
			query.setParameter("id_conta_banco", id_contaBanco);
			query.setParameter("tipo", tipo);
			query.setParameter("id_usuario", usuario);
			query.setParameter("dataInicial", dataInicial);
			query.setParameter("dataFinal", dataFinal);
			
			
			if(!query.list().isEmpty() && query.list().get(0) != null){
				return (AuxENT) query.list().get(0);
				//return new UsuarioENT;
			}else{				
				return new AuxENT(0.00);
			}			
		} catch (HibernateException e) {
			System.out.println("Erro encontrado:"+getClass().toString()+" Mesagem de Erro: "+e.toString());			 
			return new AuxENT();
		}
	}
	
	public AuxDAO() {
		// TODO Auto-generated constructor stub
	}

}
